﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace ScoreManagement
{
    public partial class ScoreFrom : Form
    {
        private string url = "server=127.0.0.1; port=3306;database=exercise;uid=root;pwd=123456";

        public ScoreFrom()
        {
            InitializeComponent();
        }

        private void ScoreFrom_Load(object sender, EventArgs e)
        {
            string _sql = "SELECT distinct s_class FROM student";
            MySqlConnection conn = new MySqlConnection(url);
            MySqlCommand cmd = new MySqlCommand(_sql, conn);
            try
            {
                conn.Open();
                MySqlDataReader dr = cmd.ExecuteReader();
                //读取专业名
                while (dr.Read())
                {
                    stuZY.Items.Add(dr[0]);
                }
                stuZY.SelectedIndex = 0;
                dr.Close();
                _sql = "SELECT KCM FROM KCB";
                cmd = new MySqlCommand(_sql, conn);
                dr = cmd.ExecuteReader();
                //读取课程名
                while (dr.Read())
                {
                   // stuKCM.Items.Add(dr[0]);
                }
                dr.Close();
            }
            finally
            {
                conn.Close();
            }
        }

        private void stuKCM_SelectedIndexChanged(object sender, EventArgs e)
        {
            string _sql = "SELECT student.s_id AS '学号',student.s_name AS '姓名',KCB.KCM AS '课程名',CJB.CJ AS '成绩'," +
               "KCB.XF AS '学分',KCB.XS AS '学时',KCB.XQ AS '开课学期' FROM student,KCB,CJB WHERE KCB.KCM='" +
               stuKCM.Text + "'AND CJB.s_id=student.s_id AND CJB.KCH=KCB.KCH";
            if (stuXH.Text.Trim() != string.Empty)
            {
                _sql += " AND student.s_id='" + stuXH.Text + "'";
            }
            MySqlConnection conn = new MySqlConnection(url);
            MySqlDataAdapter sda = new MySqlDataAdapter(_sql, conn);
            DataSet ds = new DataSet();
            sda.Fill(ds);
            scoreDGV.DataSource = ds.Tables[0].DefaultView;
        }

        private void stuXH_SelectedIndexChanged(object sender, EventArgs e)
        {
            string _sql = "SELECT student.s_id AS'学号',student.s_name AS '姓名',kcb.KCM AS '课程名',cjb.CJ AS '成绩',KCB.XF AS '学分',KCB.XS AS '学时',KCB.XQ AS '开课学期'" +
                "FROM student,KCB,CJB WHERE CJB.s_id='" + stuXH.Text + "'AND CJB.s_id=student.s_id AND CJB.KCH=KCB.KCH";
            if (stuKCM.Text.Trim() != string.Empty)
            {
                _sql += " AND KCB.KCM='" + stuKCM.Text + "'";
            }
            MySqlConnection conn = new MySqlConnection(url);
            MySqlDataAdapter sda = new MySqlDataAdapter(_sql, conn);
            DataSet ds = new DataSet();
            sda.Fill(ds);
            scoreDGV.DataSource = ds.Tables[0].DefaultView;
        }

        private void scoreDGV_RowHeaderMouseClick(object sender, DataGridViewCellMouseEventArgs e)
        {
            //获得选中的记录行
            DataGridViewRow dgvRow = scoreDGV.Rows[e.RowIndex];
            //获得行单元格集合
            DataGridViewCellCollection dgvCC = dgvRow.Cells;
            //获得单元格数据
            stuXM.Text = dgvCC[1].Value.ToString();
            stuCJ.Text = dgvCC[3].Value.ToString();
            stuXF.Text = dgvCC[4].Value.ToString();
            stuKCM.SelectedItem = dgvCC[2].Value;
        }

        private void update_btn_Click(object sender, EventArgs e)
        {
            string _sql = "SELECT COUNT(*) FROM CJB WHERE CJB.s_id='" + stuXH.Text +
                         "'AND CJB.KCH=(SELECT KCH FROM KCB WHERE KCM ='" + stuKCM.Text + "')";
            MySqlConnection conn = new MySqlConnection(url);
            MySqlCommand cmd = new MySqlCommand(_sql, conn);
            //检查是否有此学生记录，有就修改，无则添加
           
                conn.Open();
                long cnt = (long)cmd.ExecuteScalar();
                //修改记录
                if (cnt == 1)
                {
                    _sql = "UPDATE CJB SET CJB.CJ='" + stuCJ.Text + "'WHERE CJB.s_id='" + stuXH.Text +
                        "'AND CJB.KCH=(SELECT KCH FROM KCB WHERE KCM='" + stuKCM.Text + "')";
                }
                //添加新纪录
                else
                {
                    string _sql2 = "SELECT KCH FROM KCB WHERE KCM='" + stuKCM.Text + "'";
                    MySqlCommand cmd2 = new MySqlCommand(_sql2, conn);
                    _sql = "INSERT INTO CJB VALUES('" + stuXH.Text.Trim() + "','" + cmd2.ExecuteScalar() +
                        "," + int.Parse(stuCJ.Text.Trim()) + ")";
                }
                cmd = new MySqlCommand(_sql, conn);
                cmd.ExecuteNonQuery();
                stuXH_SelectedIndexChanged(null, null);
                MessageBox.Show("更新成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
            conn.Close();
        }

        private void delete_btn_Click(object sender, EventArgs e)
        {
            DialogResult ret = MessageBox.Show("确定要删除记录吗？", "删除", MessageBoxButtons.OKCancel, MessageBoxIcon.Question);
            if (ret == DialogResult.Cancel)
            {
                return;
            }
            string _sql = "DELETE FROM CJB WHERE s_id='" + stuXH.Text + "'AND KCH=(SELECT KCH FROM KCB WHERE KCM='" + stuKCM.Text + "')";
            MySqlConnection conn = new MySqlConnection(url);
            MySqlCommand cmd = new MySqlCommand(_sql, conn);
            try
            {
                conn.Open();
                int rows = cmd.ExecuteNonQuery();
                stuXH_SelectedIndexChanged(null, null);
                if (rows == 1)
                {
                    MessageBox.Show("删除成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
            }
            finally
            {
                conn.Close();
            }
        }

        private void cancel_btn_Click(object sender, EventArgs e)
        {
            this.Close();
        }
    }
}
